-- @owner: @zhanghuan96
-- @date: 2024/11/5
-- @testpoint: GMS_XMLGEN.getxmltypeTYPE语法2，查询语句测试，部分测试点合理报错
@conn PrimaryDbAmode;
--前置条件
drop extension if exists gms_xmlgen;
create extension gms_xmlgen;
set behavior_compat_options = 'bind_procedure_searchpath';
--step1:建表并创建视图;expect:成功
drop table if exists t_getxmltypetype0006;
create table t_getxmltypetype0006 ("integer" integer);
insert into t_getxmltypetype0006 values(1);
drop view if exists v_getxmltype0008;
create view v_getxmltype0008 as select * from t_getxmltypetype0006;
drop materialized view if exists m_getxmltype0008;
CREATE MATERIALIZED VIEW m_getxmltype0008 AS select count(*) from t_getxmltypetype0006;
drop materialized view if exists zl_getxmltype0008;
CREATE INCREMENTAL MATERIALIZED VIEW zl_getxmltype0008 AS SELECT * FROM t_getxmltypetype0006;
--step2:创建索引;expect:成功
drop index if exists id_getxmltype0008;
create unique index id_getxmltype0008 on t_getxmltypetype0006("integer");
--step3:GMS_XMLGEN.GETTEXT测试，查询语句为表;expect:成功
DECLARE
xml_output gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select * from t_getxmltypetype0006');
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
END;
/

--step4:不查表;expect:成功
DECLARE
xml_output gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select 1');
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
END;
/
--step5:查询视图;expect:成功
DECLARE
xml_output gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select * from v_getxmltype0008');
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
END;
/
--step6:查询物化视图;expect:成功
DECLARE
xml_output gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select * from m_getxmltype0008');
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
END;
/
--step7:增量物化视图;expect:成功
DECLARE
xml_output gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select * from zl_getxmltype0008');
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
END;
/
--step8:查对象不存在;expect:报错
drop table if exists zxl_getxmltype0008;
DECLARE
xml_output gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select * from zxl_getxmltype0008');
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
END;
/
--step9:查询语句为系统表;expect:成功
DECLARE
xml_output gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select * from pg_class limit 1',654);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
END;
/
--step10:查询对象为索引;expect:报错
DECLARE
xml_output gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select * from id_getxmltype0008',654);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
END;
/
--step11:查询语句为系统函数;expect:成功
DECLARE
xml_output gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select current_user',654);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
END;
/

--step12:查询语句加分号;expect:成功
DECLARE
xml_output gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select current_user;',654);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
END;
/
--step13:查询语句有误;expect:报错
DECLARE
xml_output gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('selects current_user;',654);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
END;
/
--step14:不是查询语句;expect:报错
DECLARE
xml_output gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('delete from t_getxmltypetype0006;',654);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
END;
/
--step15:入参为select into语句;expect:报错
drop table if exists t_getxmltypetype0006_01;
DECLARE
xml_output gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select into t_getxmltypetype0006_01 as select * from t_getxmltypetype0006;',654);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
END;
/
--step16:参数1为空串;expect:报错
DECLARE
xml_output gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('',654);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
END;
/
--step17:参数1是null;expect:报错
DECLARE
xml_output gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype(null,654);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
END;
/
--step18:参数1是不合理的语句;expect:报错
DECLARE
xml_output gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('abc',654);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
END;
/
--step19:参数1是布尔类型;expect:报错
DECLARE
xml_output gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype(true,654);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
END;
/
--step20:参数1是字符串;expect:报错
DECLARE
xml_output gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('*&^测试4564',654);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
END;
/
--step21:查询语句是双引号;expect:报错
DECLARE
xml_output gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype("select 1",654);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
END;
/
--step22:查询语句是类型转换;expect:成功
DECLARE
xml_output gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('SELECT ''fat & (rat | cat)''::tsquery;',654);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
END;
/

--step23:语法不支持;expect:报错
DECLARE
xml_output gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select 1 from dual',654);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
END;
/
--step24:清理环境;expect:成功
drop table if exists t_getxmltypetype0006 cascade;
drop table if exists t_getxmltypetype0006_01 cascade;
drop extension if exists gms_xmlgen;

